USE GESTION2
go

IF OBJECT_ID('dbo.WASP_D0004_MAESTRO_EMPRESA') IS NOT NULL 
   BEGIN
      DROP PROCEDURE dbo.WASP_D0004_MAESTRO_EMPRESA 
      IF OBJECT_ID('dbo.WASP_D0004_MAESTRO_EMPRESA') 
      IS
      NOT NULL 
         PRINT 
         '<<< FAILED DROPPING PROCEDURE dbo.WASP_D0004_MAESTRO_EMPRESA >>>' 
      ELSE
         PRINT '<<< DROPPED PROCEDURE dbo.WASP_D0004_MAESTRO_EMPRESA >>>' 
   END
go

SET ANSI_NULLS ON
go

SET QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[WASP_D0004_MAESTRO_EMPRESA] 
AS
   SELECT
      --	Cast(emp.Z07327AGE as decimal(2,0))as CODAGENCIA,
      Cast(ecuen.Z07325OPE AS decimal(2, 0))AS CODAGENCIA,
      CAST(RTRIM(CAST(Z07327cor AS CHAR)) + REPLICATE('0', 2 - LEN(CAST(
      Z07327MAT AS CHAR))) + CAST(Z07327MAT AS CHAR) AS INT) AS CLVEMPRESA,
      Cast(emp.z07327DVE AS CHAR(1)) AS DVEEMPRESA,
      Cast(pjuri.Pjrazs AS CHAR(40)) AS NOMEMPRESA,
      ( 	 	 Cast(dcuen.docall AS CHAR(40)) + 	 	 Cast(dcuen.donro AS CHAR(6)) +
      cast(dcuen.Doapar AS CHAR(6)) + 	 	 cast(comu.Z07310DSC AS CHAR(14)) + 	
      cast('' AS CHAR(14)) + 	 	  cast(edcuen.Z07339REG AS CHAR(2)) + 	 	  cast
      ('' AS CHAR(9)) 	  )AS DIREEMPRESA,
      SUBSTRING(icuen.Pendoc, 1, LEN(icuen.Pendoc) - 1) AS RUTEMPRESA,
      SUBSTRING(icuen.Pendoc, LEN(icuen.Pendoc), 1) AS DVRUTEMPRE,
      CONVERT(varchar(6), eperj.Z07324AFI, 112)AS FECAFILEMP,
      CONVERT(CHAR(8), cuen.Ctfbaj, 12)AS FECDESAEMP,
      cuen.Ctnroi AS ACTIVEMPRE,
      ecuen.Z07307COD AS COMPINEMPR,
      per.Penom AS NOMREPLEGE,
      SUBSTRING(per.Pendoc, 1, LEN(per.Pendoc) - 1) AS RUTREPLEGE,
      SUBSTRING(per.Pendoc, LEN(per.Pendoc), 1) AS DVRUTREPLE,
      totra.num_trabajadores AS NROTRABEMP,
      NULL AS TOTIMPOEMP,
      ecuen.Z07304COD AS ESTADOEMPR,
      NULL AS CONDIEMPRE,
      eperj.Z07324CSI AS CONVEEMPRE,
      NULL AS NOTAEMPRE,
      ecuen.Z07325UPE AS FULTCOTEMP,
      NULL AS FULTDECEMP,
      NULL AS TOTCOTIMPE,
      NULL AS TOTCOMPAFE,
      NULL AS MESMORAEMP,
      ecuen.Z07325BLO AS CRDTOEMPRE,
      NULL AS APORTEMPRE,
      eperj.Z07306COD AS STSETIQETA,
      NULL AS STSMODREGE,
      NULL AS FECMODREGE,
      NULL AS USRMODREGE,
      Cast(eperj.Z07315COD AS CHAR(1)) AS INDPLAAFA,
      NULL AS INDPLACSO,
      tele.tel1 AS NUMFONO1,
      tele.tel2 AS NUMFONO2,
      fax.fax1 AS NUMFAX,
      cuen.Ctejct AS CODEJE,
      NULL AS FPRICOTIZ,
      NULL AS MARCA,
      ecuen.Z07325CAN AS NTRABAFIL,
      ecuen.Z07325CAS AS FILLER,
      Cast(emp.z07327CTA AS decimal(9, 0)) AS CTABT,
      Cast(cuen.Ctccli AS decimal(2, 0))AS TIPCTABT,
      blo.Bloqueo AS BLO_EMPRE,
      CONVERT(varchar(6), eperj.Z07324FPR, 112) AS FECCONTRAEMP 
   INTO	dbo.AS_OPAPF000 
   FROM fsd008 cuen (nolock) 
   LEFT JOIN fsd006 dcuen (nolock) ON cuen.Ctnro = dcuen.Ctnro 
   LEFT JOIN NumeroTelefono tele ON cuen.Ctnro = tele.ctnro LEFT 
   JOIN fax fax ON cuen.Ctnro = fax.ctnro, z07331 edcuen (nolock) 
   LEFT JOIN z07310 comu (nolock) ON edcuen.Z07310COM = comu.Z07310COM
   , z07327 emp (nolock)
   , fsr008 icuen (nolock)
   , fsd003 pjuri (nolock)
   , z07324 eperj (nolock)
   , z07325 ecuen (nolock)
   , fsd001 per (nolock)
   , TotalTabajador totra
   , bloqueo_empresa blo 
   WHERE 	cuen.ctccli IN (1, 2) 
   AND 	cuen.Ctnro = emp.Z07327CTA 
   AND 	emp.Z07327EST = 'AC'
   AND 	cuen.Ctnro = icuen.Ctnro 
   AND 	icuen.Pendoc = pjuri.Pjndoc 
   AND 	 
   --  cuen.Ctnro *= dcuen.Ctnro and
   icuen.Pendoc = eperj.z07324NDO 
   AND 	emp.Z07327CTA = ecuen.z07325CTA 
   AND 	pjuri.Pjndoc = per.Pendoc 
   AND 	cuen.Ctnro = edcuen.Z07331CTA 
   AND 	 
   --  edcuen.Z07310COM *= comu.Z07310COM and
   --	cuen.Ctnro *= tele.ctnro and
   --  cuen.Ctnro *= fax.ctnro and
   cuen.Ctnro = totra.ctnro 
   AND 	cuen.Ctnro = blo.CLAVEEMPRESA
go



SET ANSI_NULLS OFF
go

SET QUOTED_IDENTIFIER OFF
go

IF OBJECT_ID('dbo.WASP_D0004_MAESTRO_EMPRESA') IS NOT NULL 
   PRINT '<<< CREATED PROCEDURE dbo.WASP_D0004_MAESTRO_EMPRESA >>>' 
ELSE
   PRINT '<<< FAILED CREATING PROCEDURE dbo.WASP_D0004_MAESTRO_EMPRESA >>>'
go
